package com.shuizhu.service;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.merge.OnceAbsoluteMergeStrategy;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.WriteTable;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.alibaba.excel.write.style.column.SimpleColumnWidthStyleStrategy;
import com.alibaba.excel.write.style.row.SimpleRowHeightStyleStrategy;
import com.alibaba.fastjson.JSONObject;
import com.alibaba.fastjson.parser.Feature;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.shuizhu.domain.UsersDTO;
import com.shuizhu.util.CustomRowHeightStyleStrategy;
import com.shuizhu.util.CustomSheetWriteHandler;
import org.apache.commons.collections4.map.HashedMap;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.springframework.stereotype.Service;
import org.springframework.util.ObjectUtils;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStreamWriter;
import java.nio.charset.StandardCharsets;
import java.util.*;

/**
 * 睡竹
 */
@Service
public class ExcelServiceImpl2 {
    /**
     * Excel下载
     */
    public void downExcel(HttpServletRequest reuqest, HttpServletResponse response) {
        ServletOutputStream out = null;
        ExcelWriter build = null;
        try {
            /**(1)、创建Excel核心对象 */
            out = response.getOutputStream();
            build = EasyExcel.write(out).build();
            // 创建sheet                   0表示：文件中第1个sheet页 || CustomSheetWriteHandler是去除网格线
            WriteSheet sheet = EasyExcel.writerSheet(0, "测试的sheet名称").registerWriteHandler(
                    new CustomSheetWriteHandler()).build();

            /**(2)、创建Excel页面的数据 【手动创建，工作中，这一部分是动态获取的】*/
            //1、表头数据【最上面的标题】 --topicData
            List<List<Object>> topicData = new ArrayList<>();
            ArrayList<Object> list1 = new ArrayList<>();
            list1.add("CSDN睡竹");
            topicData.add(list1);
            //2、表头备注栏【截止日期、板块内容】--secondData
            // 硬代码生成时间和版块内容【工作中，这里是动态生成的】
            String tableTime = "2022/07/05";
            List<List<Object>> secondData = new ArrayList<>();
            List<Object> list2 = new ArrayList<>();
            list2.add("截止日期：" + tableTime);
            secondData.add(list2);
            //3、生成数据表格的标题栏内容【这里的元素个数必须与表格保持一致】
            List<List<String>> head = new ArrayList<>();
            List<String> head1 = new ArrayList<>();
            head1.add("序号");
            List<String> head2 = new ArrayList<>();
            head2.add("检查项目");
            List<String> head3 = new ArrayList<>();
            head3.add("检查内容");
            List<String> head4 = new ArrayList<>();
            head4.add("检查方法（或依据）");
            List<String> head5 = new ArrayList<>();
            head5.add("检查评价");
            head5.add("符合");
            List<String> head6 = new ArrayList<>();
            head6.add("检查评价");
            head6.add("不符合及主要问题");
            head.add(head1);
            head.add(head2);
            head.add(head3);
            head.add(head4);
            head.add(head5);
            head.add(head6);
            //4、获取数据表格中的数据
            List<List<Object>> data = getData();
            //5、获取数据表格的总列数，下面需要用到
            int size = data.get(0).size();


            String opinion = "检查考核意见：";
            List<List<Object>> forthData = new ArrayList<>();
            List<Object> list4 = new ArrayList<>();
            list4.add(opinion);
            forthData.add(list4);

            String chkUser = "检查责任人：";
            List<List<Object>> fifthData = new ArrayList<>();
            List<Object> list5 = new ArrayList<>();
            list5.add(chkUser);
            fifthData.add(list5);


            /**(3)、设置3个表格的样式 */
            //1、设置表头样式【最上面的标题】
            /**
             * 1.1、 合并单元格 【四个参数】
             * 参数1：合并开始的第一行     【0：表示第一行】
             * 参数2：和平结束的最后一行   【0：表示第一行，0-0=0，表示没有合并行】
             * 参数3：合并开始的第一列     【0：表示第一列】
             * 参数4：合并开始的最后一列   【size-1：表示合并的列数与数据表格的列数一致】
             */
            OnceAbsoluteMergeStrategy mergeStrategy = new OnceAbsoluteMergeStrategy(0, 0, 0, size - 1);
            //1.2、设置内容居中
            WriteCellStyle contentStyle = new WriteCellStyle();
            //垂直居中
            contentStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            //水平居中
            contentStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
            WriteFont writeFont = new WriteFont();
            //加粗
            writeFont.setBold(true);
            //字体大小为16
            writeFont.setFontHeightInPoints((short) 16);
            contentStyle.setWriteFont(writeFont);
            // 单元格策略 参数1为头样式【不需要头部，设置为null】，参数2位表格内容样式
            HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(null, contentStyle);

            /*表格4样式*/
            OnceAbsoluteMergeStrategy mergeStrategy4 = new OnceAbsoluteMergeStrategy(15, 15, 0, size - 1);
            WriteCellStyle contentStyle4 = new WriteCellStyle();
            WriteFont writeFont4 = new WriteFont();
            writeFont4.setFontHeightInPoints((short) 10);
            contentStyle4.setWriteFont(writeFont4);
            // 单元格策略 参数1为头样式【不需要头部，设置为null】，参数2位表格内容样式
            HorizontalCellStyleStrategy horizontalCellStyleStrategy4 = new HorizontalCellStyleStrategy(null, contentStyle4);


            /*表格5样式*/
            OnceAbsoluteMergeStrategy mergeStrategy5 = new OnceAbsoluteMergeStrategy(16, 16, 0, size - 1);
            WriteCellStyle contentStyle5 = new WriteCellStyle();
            contentStyle5.setVerticalAlignment(VerticalAlignment.CENTER);
            contentStyle5.setHorizontalAlignment(HorizontalAlignment.RIGHT);
            WriteFont writeFont5 = new WriteFont();
            writeFont5.setFontHeightInPoints((short) 10);
            contentStyle5.setWriteFont(writeFont5);
            // 单元格策略 参数1为头样式【不需要头部，设置为null】，参数2位表格内容样式
            HorizontalCellStyleStrategy horizontalCellStyleStrategy5 = new HorizontalCellStyleStrategy(null, contentStyle5);


            //2、设置表头备注栏样式【截止日期、板块内容】
            /**
             * 2.1、合并单元格
             * 两个1表示合并在第二行，且没有合并行
             * 从第一列开始，合并至数据表格的总列数（长度）
             */
            OnceAbsoluteMergeStrategy mergeStrategy2 = new OnceAbsoluteMergeStrategy(1, 1, 0, size - 1);
            //2。1、单元格样式
            WriteCellStyle contentStyle2 = new WriteCellStyle();
            // 设置内容自动换行
            contentStyle2.setWrapped(true);
            // 字体样式
            WriteFont writeFont2 = new WriteFont();
            writeFont2.setFontHeightInPoints((short) 10);
            contentStyle2.setWriteFont(writeFont2);
            HorizontalCellStyleStrategy horizontalCellStyleStrategy2 = new HorizontalCellStyleStrategy(null, contentStyle2);

            //3、设置数据表格的样式
            //  ---------- 头部样式 ----------
            WriteCellStyle headStyle = new WriteCellStyle();
            // 字体样式
            WriteFont headFont = new WriteFont();
            headFont.setFontHeightInPoints((short) 11);
            headStyle.setWriteFont(headFont);
            // 背景颜色
            //headStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
            //headStyle.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.index);

            //  ---------- 内容样式 ----------
            WriteCellStyle bodyStyle = new WriteCellStyle();
            //StyleProperty styleProperty = new StyleProperty();
            // 字体样式
            WriteFont bodyFont = new WriteFont();
            bodyFont.setFontHeightInPoints((short) 10);
            bodyStyle.setWriteFont(bodyFont);
            // 设置边框
            // bodyStyle.setBorderTop(BorderStyle.DOUBLE);
            bodyStyle.setBorderLeft(BorderStyle.THIN);
            bodyStyle.setBorderRight(BorderStyle.THIN);
            bodyStyle.setBorderBottom(BorderStyle.THIN);
            bodyStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
            bodyStyle.setVerticalAlignment(VerticalAlignment.CENTER);

            // styleProperty.setColumnWidth(20); // 设置列宽，单位为字符宽度
            // styleProperty.setRowHeight((short) 300); // 设置行高，单位为 1/20 磅
            // 创建策略
            HorizontalCellStyleStrategy dataTableStrategy = new HorizontalCellStyleStrategy(headStyle, bodyStyle);

            /**
             * (4)、统一设置行高
             */
            // 设置表头行高【最上面的标题】  参数1：表头行高为0【不需要表头】    参数2：内容行高为28
            SimpleRowHeightStyleStrategy rowHeightStrategy1 = new SimpleRowHeightStyleStrategy((short) 0, (short) 28);
            // 设置表头备注栏行高【截止日期、板块内容】
            SimpleRowHeightStyleStrategy rowHeightStrategy2 = new SimpleRowHeightStyleStrategy((short) 0, (short) 25);
            // 设置数据表格的行高   null表示使用原来的行高
            SimpleRowHeightStyleStrategy rowHeightStrategy3 = new SimpleRowHeightStyleStrategy(null, (short) 18);

            /**(5)、生成页面中的3个表格
             *  0 , 1 , 2 是表格的排序(从上往下)
             *  上面设置的样式，合并。。。都需要在这里关联对应的表格
             */
            // 生成表格1 ----页面中最上方的大标题
            WriteTable topicTable = EasyExcel.writerTable(0).registerWriteHandler(rowHeightStrategy1).registerWriteHandler(mergeStrategy).registerWriteHandler(horizontalCellStyleStrategy).needHead(false).build();
            // 生成表格2 ----表头备注栏【截止日期、板块内容】
            WriteTable secondTable = EasyExcel.writerTable(1).registerWriteHandler(rowHeightStrategy2).registerWriteHandler(mergeStrategy2).registerWriteHandler(horizontalCellStyleStrategy2).needHead(false).build();
            // 生成表格3 ----user表格
            WriteTable dataTable = EasyExcel.writerTable(2).registerWriteHandler(new SimpleColumnWidthStyleStrategy(25)).registerWriteHandler(new CustomRowHeightStyleStrategy(10)).registerWriteHandler(dataTableStrategy).head(head).needHead(true).build();

            //WriteTable forthTable = EasyExcel.writerTable(3).registerWriteHandler(rowHeightStrategy1).registerWriteHandler(mergeStrategy4).registerWriteHandler(horizontalCellStyleStrategy4).needHead(false).build();

           // WriteTable fifthTable = EasyExcel.writerTable(4).registerWriteHandler(rowHeightStrategy1).registerWriteHandler(mergeStrategy5).registerWriteHandler(horizontalCellStyleStrategy5).needHead(false).build();

            // 新增表格4 ----新的两列表格
            //addTwoColumnTable(build, sheet);

            /**(6)、把数据填充至各个表格 */
            build.write(topicData, sheet, topicTable);
            build.write(secondData, sheet, secondTable);
            build.write(data, sheet, dataTable);

            //build.write(forthData,sheet,forthTable);

            //build.write(fifthData,sheet,fifthTable);

            /**(7) 生成文件 */
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");
            response.setHeader("Content-disposition", "attachment;filename=" + "测试Excel" + ".xlsx");
        } catch (Exception e) {
            e.printStackTrace();
            /**
             * 前后端处理下载失败异常
             * 该部分必须与【流关闭操作】处于同一 【try-catch-finally】中，否则无法返回错误信息给前端
             */
            response.reset();
            response.setHeader("content-type", "text/html;charset=utf-8");
            try (OutputStreamWriter writer = new OutputStreamWriter(out, StandardCharsets.UTF_8)) {
                // 组装JSON
                Map<String, Object> map = new HashedMap<>();
                map.put("retCode", "9999");
                map.put("retMsg", "Excel下载失败：" + e.getMessage());
                String json = new ObjectMapper().writeValueAsString(map);
                if (!ObjectUtils.isEmpty(writer) && !ObjectUtils.isEmpty(json)) {
                    //返回错误信息给前端
                    writer.write(json);
                    writer.flush();
                }
            } catch (Exception e2) {
                throw new ClassCastException("response.getOutputStream()语句异常");
            }

        } finally {
            //关闭所有的流

            build.finish();

            if (!ObjectUtils.isEmpty(out)) {
                try {
                    out.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }

    }

    /**
     * 模拟数据库查询，获取user数据
     */
    public List<UsersDTO> getUsers() {
        List<UsersDTO> users = new ArrayList<>();
        for (int i = 0; i < 10; i++) {
            UsersDTO usersDTO = new UsersDTO();
            usersDTO.setNum(123);
            usersDTO.setItem("1000" + i);
            usersDTO.setContent("第" + i + "个用户");
            usersDTO.setMethod(18 + i + "");
            usersDTO.setCheckResult("13231231231");
            usersDTO.setCheckResultReason("放假啊的房间里附件");
            users.add(usersDTO);
        }
        return users;
    }

    /**
     * 不是填充Excel，而是直接往Excel中写
     * 需要从users数据集中，获取到value的部分，不需要key
     */
    public List<List<Object>> getData() {
        List<UsersDTO> users = getUsers();
        List<List<Object>> data = new ArrayList<>();
        for (UsersDTO user : users) {
            //把user转为map
            String s = JSONObject.toJSONString(user);
            Map jsonMap = JSONObject.parseObject(s, LinkedHashMap.class, Feature.OrderedField);
            //获取map中的value，组装成list
            Collection values = jsonMap.values();
            List<Object> list = new ArrayList<>(values);
            data.add(list);
        }
        return data;
    }

    /**
     * 新增一个表格，左侧是表头，右侧是数据，这个表格只有两列，一列是表头，一列是数据
     */
    public void addTwoColumnTable(ExcelWriter build, WriteSheet sheet) {
        // 1. 定义新表的表头数据和数据部分
        List<List<Object>> twoColumnHeader = new ArrayList<>();
        List<Object> headerRow = Arrays.asList("表头1", "表头2"); // 替换为实际的表头
        twoColumnHeader.add(headerRow);

        // 新增 "123" 作为表头2的数据
        List<Object> headerRow123 = Arrays.asList("", "123"); // 添加到表头2的数据中
        twoColumnHeader.add(headerRow123);

        List<List<Object>> twoColumnData = new ArrayList<>();
        // 替换为实际的数据
        // 例如：
        // twoColumnData.add(Arrays.asList("数据1", "数据A"));
        // twoColumnData.add(Arrays.asList("数据2", "数据B"));

        // 2. 设置新表的样式（可能需要重新定义样式）
        // 新表的样式示例
        WriteCellStyle twoColumnStyle = new WriteCellStyle();
        WriteFont twoColumnFont = new WriteFont();
        twoColumnFont.setFontHeightInPoints((short) 11);
        //twoColumnFont.setColor(FontColor.RED); // 通过设置颜色改变字体颜色
        twoColumnStyle.setWriteFont(twoColumnFont);

        // 新表的策略示例
        HorizontalCellStyleStrategy twoColumnStrategy = new HorizontalCellStyleStrategy(null, twoColumnStyle);

        // 3. 创建新表格并写入数据
        WriteTable twoColumnTable = EasyExcel.writerTable(3) // 选择适当的索引
                .registerWriteHandler(twoColumnStrategy)
                .registerWriteHandler(new SimpleColumnWidthStyleStrategy(20))
                .registerWriteHandler(new CustomRowHeightStyleStrategy(10))
                .needHead(true)
                .build();

        // 将新表格写入现有表单
        build.write(twoColumnHeader, sheet, twoColumnTable);
        build.write(twoColumnData, sheet, twoColumnTable);
    }

}
